package br.com.weysolucoes.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import br.com.weysolucoes.conexao.ConexaoBD;
import br.com.weysolucoes.constantes.ConstantesSQL;
import br.com.weysolucoes.dto.EvidenciaNcDTO;

public class EvidenciaNcDAO {

	ConstantesSQL constantesSQL = new ConstantesSQL();

	public boolean inserirEvidenciaNc(EvidenciaNcDTO evidenciaNcDTO)
			throws SQLException {

		ConexaoBD conexaoBD = new ConexaoBD();
		Statement st = null;
		ResultSet rs = null;

		try {

			st = conexaoBD.connection.createStatement();
			PreparedStatement preparedStatement = conexaoBD.connection
					.prepareStatement(constantesSQL.getSqlInsertEvidencia());
			preparedStatement.setInt(1, evidenciaNcDTO.getMatUsuario());
			preparedStatement.setString(2, evidenciaNcDTO.getCodModelo());
			preparedStatement.setInt(3, evidenciaNcDTO.getCodNaoConformidade());
			preparedStatement.setInt(4, evidenciaNcDTO.getNumSerie());
			preparedStatement.execute();
			return true;

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(EvidenciaNcDAO.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}
	}
	
	public boolean inserirResolucaoNc(EvidenciaNcDTO evidenciaNcDTO)
			throws SQLException {

		ConexaoBD conexaoBD = new ConexaoBD();
		Statement st = null;
		ResultSet rs = null;

		try {

			st = conexaoBD.connection.createStatement();
			PreparedStatement preparedStatement = conexaoBD.connection
					.prepareStatement(constantesSQL.getSqlInsertResolucao());
			preparedStatement.setInt(1, evidenciaNcDTO.getCodEvidencia());
			preparedStatement.setInt(2, evidenciaNcDTO.getMatUsuarioResolucao());
			preparedStatement.setInt(3, evidenciaNcDTO.getCodNaoConformidade());
			preparedStatement.setString(4, evidenciaNcDTO.getDesResolucao());
			preparedStatement.execute();
			return true;
			
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(EvidenciaNcDAO.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}
	}

	// lista todas as evidencias de defeito cadastrados no banco de dados
	public List<EvidenciaNcDTO> listEvidenciaNcDTO() throws SQLException {

		ConexaoBD conexaoBD = new ConexaoBD();
		ArrayList<EvidenciaNcDTO> lista = new ArrayList<EvidenciaNcDTO>();
		Statement st = null;
		ResultSet rs = null;

		try {
			st = conexaoBD.connection.createStatement();

			String sql = constantesSQL.getSqlSelectEvidencia();
			rs = st.executeQuery(sql);

			while (rs.next()) {

				EvidenciaNcDTO evidenciaNcDTO = new EvidenciaNcDTO();
				evidenciaNcDTO.setCodEvidencia(rs.getInt(1));
				evidenciaNcDTO.setMatUsuario(rs.getInt(2));
				evidenciaNcDTO.setNmUsuario(rs.getString(3));
				evidenciaNcDTO.setCodModelo(rs.getString(4));
				evidenciaNcDTO.setCodNaoConformidade(rs.getInt(5));
				evidenciaNcDTO.setNumSerie(rs.getInt(6));
				evidenciaNcDTO.setDtHrEvidencia(rs.getString(7));
			
				lista.add(evidenciaNcDTO);
			}

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(EvidenciaNcDAO.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
				if (conexaoBD.connection != null) {
					conexaoBD.connection.close();
				}
			} catch (SQLException ex) {
				Logger lgr = Logger.getLogger(EvidenciaNcDAO.class.getName());
				lgr.log(Level.WARNING, ex.getMessage(), ex);
			}

		}

		return lista;
	}

	// Remove o cadastrado no banco de dados
	public boolean deletarEvidenciaNc(EvidenciaNcDTO evidenciaNcDTO)
			throws SQLException {

		ConexaoBD conexaoBD = new ConexaoBD();

		Statement st = null;
		ResultSet rs = null;

		try {

			st = conexaoBD.connection.createStatement();
			PreparedStatement preparedStatement = conexaoBD.connection
					.prepareStatement(ConstantesSQL.getSqlDeleteEvidencia());
			preparedStatement.setInt(1, evidenciaNcDTO.getCodEvidencia());
			preparedStatement.executeUpdate();
			return true;

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(EvidenciaNcDAO.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}
	}

	// Editar Evidencias cadastrados no banco de dados
	public boolean editarEvidenciaNc(EvidenciaNcDTO evidenciaNcDTO)
			throws SQLException {

		ConexaoBD conexaoBD = new ConexaoBD();
		Statement st = null;
		ResultSet rs = null;

		try {

			st = conexaoBD.connection.createStatement();

			PreparedStatement preparedStatement = conexaoBD.connection
					.prepareStatement(ConstantesSQL.getSqlUpdateEvidencia());
			preparedStatement.setInt(1, evidenciaNcDTO.getMatUsuario());
			preparedStatement.setString(2, evidenciaNcDTO.getCodModelo());
			preparedStatement.setInt(3, evidenciaNcDTO.getCodNaoConformidade());
			preparedStatement.setInt(4, evidenciaNcDTO.getNumSerie());
			preparedStatement.setInt(5, evidenciaNcDTO.getCodEvidencia());
			preparedStatement.executeUpdate();
			return true;

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(EvidenciaNcDAO.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}
	}

}
